# -*-encoding=utf-8 -*-
# 编写读取登陆的数据文件函数
import json, pymysql, xlrd, logging
from logging import handlers

import yaml

import config
import os, platform
from parameterized import parameterized

def read_json_data(filename):
    """
    :param filename:  要传入的外部登陆的json数据文件路径
    :return:
    """
    with open(filename, mode='r', encoding="utf-8") as f:
        jsonData = json.load(f)  # 将读取的数据流f，转化为json数据
        result_list = list()  # 定义空列表存放转化之后的数据
        for login_data in jsonData:
            result_list.append(tuple(login_data.values()))  # 将列表字典数据，转化为列表元组数据，并添加到空列表当中，整合成parameterized所需要的列表元素数据
        print(result_list)
    return result_list


class PymysqlUtils:

    def __init__(self, host=config.MYSQL_HOST, username=config.MYSER_USER, password=config.MYSQL_PASSWORD, port=config.MYSQL_PORT,
                 database="ssm_lagou_edu",
                 charset="UTF8"):
        self.host = host
        self.port = port
        self.database = database
        self.user = username
        self.password = password
        self.charset = charset
        self.conn, self.cursor = None, None

    def __enter__(self):
        self.conn = pymysql.connect(host=self.host,
                                    user=self.user,
                                    password=self.password,
                                    database=self.database,
                                    port=self.port,
                                    charset=self.charset)

        self.cursor = self.conn.cursor()
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.cursor:
            self.cursor.close()

        if self.conn:
            self.conn.close()


class ExcelUtils:
    def __init__(self):
        pass

    @classmethod
    def read_excel_data(self, filename):
        book = xlrd.open_workbook(filename)
        sheet = book.sheet_by_index(0)

        params_data = []
        for i in range(1, sheet.nrows):  # sheet.nrows 获取总行数
            params_data.append(tuple(sheet.row_values(i)))  # sheet.row_values(i) 获取i行的数据
        return params_data


def logging_init():
    # 初始化日志器
    logger = logging.getLogger()
    # 设置日志等级
    logger.setLevel(logging.INFO)
    # 添加控制器
    stream_handler = logging.StreamHandler()
    file_handler = logging.handlers.TimedRotatingFileHandler(config.BASE_DIR + "/logs/lagou_log.log", when='h',
                                                             interval=1, backupCount=3, encoding="utf-8")
    # 设置日志格式
    fmt = "%(asctime)s %(levelname)s [%(name)s] [%(thread)d] [%(threadName)s] [ %(filename)s %(funcName)s %(lineno)d ] %(message)s "
    formatter = logging.Formatter(fmt)
    # 将日志格式添加到控制器
    stream_handler.setFormatter(formatter)
    file_handler.setFormatter(formatter)
    # 将控制器添加到日志器
    logger.addHandler(stream_handler)
    logger.addHandler(file_handler)

    return logger


class DataManger:
    """
    管理被测试服务器的数据，一般先备份、在初始化数据、测试结束后，还原数据
    """

    def __init__(self):
        pass

    @classmethod
    def init_data(self):
        """
        初始化数据到测试之前的状态
        :return:
        """
        print("初始化测试数据...")
        command = os.path.abspath(config.MYSQLCLI)
        logging.info(command)
        test_data = config.BASE_DIR + "/backup/test_data.sql"
        sql = f"{command} -h {config.MYSQL_HOST} -u root -p{config.MYSQL_PASSWORD} -P {config.MYSQL_PORT} ssm_lagou_edu < {test_data}"
        logging.info(f"初始化的sql为：{sql}")
        os.system(sql)

        print("初始化测试数据结束...")

    @classmethod
    def back_data(self):
        """
        备份现有数据
        :return:
        """
        print("开始备份数据...")
        command = os.path.abspath(config.MYSQLDUMP)
        logging.info(command)
        backup = config.BASE_DIR + "/backup/backdb.sql"
        sql = f"{command} -u root -h {config.MYSQL_HOST} -p{config.MYSQL_PASSWORD} -P {config.MYSQL_PORT} --databases ssm_lagou_edu > {backup}"
        logging.info(f"备份数据的sql为：{sql}")
        os.system(sql)
        print(f"备份数据结束，备份的数据文件名称：{backup}")

    @classmethod
    def recovery_data(self):
        """
        恢复数据
        :return:
        """
        print("开始恢复数据...")
        command = os.path.abspath(config.MYSQLCLI)
        logging.info(command)
        recovery_data = config.BASE_DIR + "/backup/backdb.sql"
        sql = f"{command} -h {config.MYSQL_HOST} -u root -p{config.MYSQL_PASSWORD} -P {config.MYSQL_PORT} ssm_lagou_edu < {recovery_data}"
        logging.info(f"恢复数据的SQL为：{sql}")
        os.system(sql)
        print("恢复数据结束...")


if __name__ == '__main__':
    # DataManger.init_data()
    DataManger.recovery_data()